Livrable 2

Equipe 10 :

Sommaire :

I. Livrable 1

1. Introduction
2. Le dictionnaire des données
3. Modèle Conceptuel de Données
4. Modèle Logique de Données
5. Modèle Physique de Données

II. Livrable 2

1. Peuplement
2. Requêtes
3. Fonctionnalités
4. Conclusion

I. Livrable 1

1. Introduction

Pour rappel, le projet vise à concevoir une base de données centralisée pour stocker et analyser les données sur la qualité de l’air en France.

Phase 1 :
Nous devons livrer le dictionnaire des données, le MCD, le MLD, le script SQL et les arbres algébriques des différentes requêtes demandées.

Expliquer ce qu’est :

Relations entre MCD, MLD et MPD

2. Le dictionnaire des données

Un dictionnaire de données joue un rôle essentiel dans la création d’une base de données en fournissant une description structurée et normalisée des éléments qui la composent. Il définit les informations sur les tables, les champs, les types de données et les relations. Ce référentiel centralisé facilite la cohérence, la maintenance, la documentation et la gouvernance des données, tout en assurant une communication claire entre tous les membres du projet et du client.

Le dictionnaire de données joue également un rôle important dans l’élaboration de la matrice des dépendances et du Modèle Conceptuel de Données (MCD).

Vous pourrez le trouver dans notre dossier ci-joint.

3. Modèle Conceptuel de Données

Nous avons schématiser le Modèle Conceptuel de Données (MCD) grâce à l’application Looping, qui permet de représenter graphiquement celui-ci, ainsi que faciliter les étapes de conception suivantes.

Notre MCD est composé de 10 tables et 19 associations.

Description des Tables

  1. Personnel

    • Attributs : Identifiant du personnel, Nom du personnel, Prénom du personnel, Dernier diplôme obtenu.
    • Description : Cette table contient les informations de base sur les employés ou le personnel de l’agence.

  2. Adresse

    • Attributs : Identifiant de l’adresse, Numéro de l’adresse, Rue de l’adresse, Complément de l’adresse.
    • Description : Cette table stocke les informations d’adresse, qui peuvent être liées à différentes entités comme le personnel ou les agences.

  3. Agence

    • Attributs : Identifiant de l’agence, Région de l’agence.
    • Description : Cette table représente les différentes agences, chacune identifiée par un identifiant unique et associée à une région spécifique.

  4. Capteur

    • Attributs : Identifiant du capteur, État du capteur.
    • Description : Cette table contient les informations liés aux capteurs.

  5. Gaz

    • Attributs : Identifiant du gaz, Nom du gaz, Symbole du gaz, Type du gaz.
    • Description : Cette table stocke les informations sur les différents types de gaz surveillés.

  6. Rapport

    • Attributs : Identifiant du rapport, Titre du rapport, Analyse du rapport.
    • Description : Cette table contient les rapports rédigés, chacun ayant une analyse associée.

  7. Relève

    • Attributs : Identifiant du relevé, Donnée relevée.
    • Description : Cette table stocke les relevés de données effectués par les capteurs.

  8. Dates

    • Attributs : Identifiant de la date, Date.
    • Description : Cette table contient les informations de date, utilisées pour enregistrer les événements temporels.

  9. Poste

    • Attributs : Identifiant du poste, Nom du poste.
    • Description : Cette table représente les différents postes.

  10. Ville

    • Attributs : Identifiant de la ville, Nom de la ville, Code postal de la ville.
    • Description : Cette table représente les informations à propos des différentes villes.

Associations et Cardinalités

  1. Habiter

    • Entités : Personnel, Adresse.
    • Cardinalité : 1,1 (Personnel) à 0,n (Adresse).
    • Description : Un personnel peut habiter à une adresse, mais un personnel ne peut habiter à plusieurs adresses.
      Une adresse peut être associée à une seul personne et une adresse peut être associée à plusieurs personnes.

  2. Gérer

    • Entités : Personnel, Capteur.
    • Cardinalité : 0,n (Personnel) à 0,1 (Capteur).
    • Description : Un personnel peut ne pas gérer de capteurs et un personnel peut gérer plusieurs capteurs.
      Un capteur est géré par un seul personnel, mais un capteur ne peut pas être géré par plusieurs personnes.

  3. Travailler

    • Entités : Personnel, Agence.
    • Cardinalité : 1,1 (Personnel) à 0,n (Agence).
    • Description : Un personnel est obligé de travailler dans au moins une agence et un personnel peut travailler dans une seule agence.
      Une agence n’est pas obligé d’avoir du personnel mais une agence peut avoir plusieurs personnels.

  4. Rédiger

    • Entités : Personnel, Rapport.
    • Cardinalité : 0,n (Personnel) à 1,n (Rapport).
    • Description : Un personnel peut avoir rédigé aucun rapport et un personnel peut rédiger plusieurs rapports.
      Un rapport est obligatoirement rédigé par un personnel mais un rapport peut être rédigé par plusieurs personnels.

  5. Naître

    • Entités : Personnel, Date.
    • Cardinalité : 1,1 (Personnel) à 0,n (Date).
    • Description : Un personnel est né à une seule date spécifique.
      Une date ne correspond pas obligatoirement à une date de naissance d’un membre du personnel mais plusieurs personnes peuvent être nées le même jour.

  6. Débuter

    • Entités : Personnel, Date.
    • Cardinalité : 1,1 (Personnel) à 0,n (Date).
    • Description : Un personnel débute son travail à une seule date spécifique. Une date ne correspond pas obligatoirement à la date de début d’un membre du personnel mais plusieurs personnes peuvent débuter le même jour.

  7. Dater

    • Entités : Rapport, Date.
    • Cardinalité : 1,1 (Rapport) à 0,n (Date).
    • Description : Un rapport est daté à une seule date spécifique.
      Une date ne correspond pas obligatoirement à la date de rédaction d’un rapport mais plusieurs rapports peuvent être datés le même jour.

  8. Se situer

    • Entités : Capteur, Adresse.
    • Cardinalité : 1,1 (Capteur) à 0,n (Adresse).
    • Description : Un capteur est situé à une seule adresse spécifique.
      Une adresse ne situe pas obligatoirement un capteur mais une adresse peut avoir plusieurs capteurs.

  9. Installer

    • Entités : Capteur, Agence.
    • Cardinalité : 1,1 (Capteur) à 0,n (Agence).
    • Description : Un capteur est obligatoirement attaché à une agence et un capteur est attaché à qu’une seule agence.
      Une agence n’est pas obligé d’avoir de capteurs mais une agence peut avoir plusieurs capteurs.

  10. Effectuer

    • Entités : Capteur, Relevé
    • Cardinalité : 0,n (Capteur) à 1,1 (Relevé).
    • Description : Un capteur n’est pas obligé de faire un relevé mais un capteur peut effectuer plusieurs relevés.
      Un relevé est effectué par un seul capteur.

  11. Identifier

    • Entités : Gaz, Relevé.
    • Cardinalité : 0,1 (Gaz) à 1,1 (Relevé).
    • Description : Un gaz n’est pas obligé d’être identifié par un relevé mais un gaz peut être identifié par qu’un seul relevé. Un relevé peut identifier qu’un seul gaz.

  12. Rapporter

    • Entités : Rapport, Relevé.
    • Cardinalité : 1,n (Rapport) à 0,n (Relevé).
    • Description : Un rapport est obligatoirement rapporté par un relevé mais un rapport peut être rapporté par plusieurs relevés.
      Un relevé n’est pas obligé de rapporter un rapport mais un relevé peut rapporter plusieurs rapports.

  13. Localiser

    • Entités : Adresse, Agence
    • Cardinalité : 0,1 (Adresse) à 1,1 (Agence).
    • Description : Une adresse ne correspond pas obligatoirement à une agence et une adresse peut correspondre une seule agence.
      Une agence se localise à une seule adresse.

  14. Faire

    • Entités : Agence, Relevé.
    • Cardinalité : 0,n (Agence) à 1,1 (Relevé).
    • Description : Une agence ne fait pas obligatoirement un relevé mais une agence peut faire plusieurs relevés.
      Un relevé est fait par une seule agence.

  15. Capter

    • Entités : Capteur, Gaz.
    • Cardinalité : 1,1 (Capteur) à 0,n (Gaz).
    • Description : Un capteur capte qu’un seul gaz.
      Un gaz n’est pas obligatoirement capté par un capteur mais un gaz peut être capté par plusieurs capteurs.

  16. Dater

  1. Rédiger
  1. Occuper
  1. Se localiser

4. Modèle Logique de Données

À partir du MCD, nous pouvons transformer celui-ci en MLD sur Looping, et celui-ci nous renvoie le MLD suivant sans erreur, nous pouvons donc conclure que celui-ci est correct.

À noter que plusieurs clés étrangères ne sont pas correctement nommées, comme id_date et id_date_1 dans la table Personnel. En effet, la conversion du MCD au MLD est automatique, donc le logiciel les a nommé par défaut. Nous ne pouvons pas les modifier donc nous les modifierons dans le script SQL.

5. Modèle Physique de Données

Nous avons joint le Modèle Physique de Données (MPD) en document texte, nous avons construit celui-ci encore une fois grâce à l’aide de Looping.

II. Livrable 2

Phase 2 :
Nous devons livrer les requêtes demandées (en langage SQL), des explications sur la génération et le peuplement de la base de données finale (origine des données, difficultés rencontrées) et les fonctionnalités présentes ou absentes par rapport à la demande et aux spécifications techniques.

1. Peuplement

Il nous a été demandé d’imaginer un jeu de données à intégrer dans la base de données pour tester et vérifier les fonctionnalités de celle-ci.
Pour cela, nous sommes partis sur la rédaction d’un script en langage Python, car cette méthode possède plusieurs avantages :

Exemple de peuplement de la table Gaz :

import random
import csv
from datetime import datetime, timedelta

# Initialisation des lignes SQL
sql_lignes = []

# Données à insérer
gaz_data = [
    (1, "Protoxyde d azote", "N2O", "GES"),
    (2, "Ozone troposphérique", "O3", "GES"),
    (3, "Méthane", "CH4", "GES"),
    (4, "Hydrofluorocarbures", "HFC", "GESI"),
    (5, "Hexafluorure de soufre", "SF6", "GESI"),
    (6, "Dioxyde de carbone", "CO2", "GES"),
    (7, "Ammoniac", "NH3", "GESI"),
    (8, "Perfluorocarbures", "PFC", "GES")
]

# Génération des lignes INSERT
for id_gaz, nom, sigle, typ in gaz_data:
    ligne = f"INSERT INTO gaz (id_gaz, gaz_nom, gaz_sigle, gaz_type) VALUES ({id_gaz}, '{nom}', '{sigle}', '{typ}');"
    sql_lignes.append(ligne)

Pour la génération des rues d’adresse, des noms et prénoms, ainsi que les diplôme du personnel, nous avons demandé à une intelligence artificielle de générer une liste de possibilités pour chaque, que nous avons associés aléatoirement pour obtenir un résultat réaliste, bien que contenant quelques anomalies comme des personnes possédant le même nom et prénom, cependant ces anomalies n’ont aucun impact grâce à l’utilisation d’un identifiant unique.

Exemple de création d’adresses :

import random

nombre_de_villes = 4
nombre_de_rues_souhaites = 50

adresses = []
noms_rues = [
    "Rue de la République", 
    "Avenue Victor Hugo", 
    "Boulevard Haussmann",
]

for i in range(nombre_de_rues_souhaites):
    numero = random.randint(1, 200)
    rue = random.choice(noms_rues)
    id_ville = random.randint(1, nombre_de_villes)
    adresses.append((f"{numero}", rue, id_ville))

# Affichage du résultat
print("adresses = [")
for adresse in adresses:
    print(f"    {adresse},")
print("]")

Pour s’assurer par exemple qu’un capteur relié à l’agence de Paris est bien géré par un agent technique de Paris, nous avons, lors de la création du personnel, ajouté chaque agent à une liste créée pour chaque ville, puis nous avons attribué un agent de cette liste aléatoirement à chaque capteur.
Ces données simulées nous ont permis de créer un environnement réaliste temporaire pour notre base de données, en fournissant des valeurs fictives.

Le fichier permettant de créer et peupler la BDD se nomme “scipt_complet.sql”, ce fichier est séparé en plusieurs fichiers plus petit dans le dossier “Scripts SQL” et possèdent l’ordre auxquels ils doivent être exécuté.

2. Requêtes

1. Liste de l’ensemble des agences

Select *
From Agence;

2. Liste de l’ensemble du personnel technique de l’agence de Bordeaux

Select id_personnel, pers_nom, pers_prenom
From Personnel
Join Agence On Agence.id_agence = Personnel.id_agence
Join Adresse On Adresse.id_adresse = Agence.id_adresse
Join Ville On Ville.id_ville = Adresse.id_ville
Where Ville.ville_nom = "Bordeaux";

3. Nombre total de capteurs déployés

Select Count(id_capteur)
From Capteur;

4. Liste des rapports publiés entre 2018 et 2022

Select *
From Rapport
Natural Join Dates
Where d_date Between "2018-01-01" And "2022-12-31";

5. Afficher les concentrations de CH4 (en ppm) dans les régions « Ile-de-France », « Bretagne » et « Occitanie » en mai et juin 2023

Select id_releve, rel_donnee
From Releve
Join Agence On Agence.id_agence = Releve.id_agence 
Join Dates On Dates.id_date = Releve.id_date
Join Gaz On Gaz.id_gaz = Releve.id_gaz
Where Gaz.gaz_sigle = "CH4"
And Dates.d_date Between '2023-05-01' And '2023-06-30'
And Agence.ag_region In ("Île de France", "Bretagne","Occitanie");

6. Liste des noms des agents techniques maintenant des capteurs concernant les gaz à effet de serre provenant de l’industrie (GESI)

Select Distinct Personnel.id_personnel, Personnel.pers_nom, Personnel.pers_prenom 
From Personnel
Join Poste On Poste.id_poste = Personnel.id_poste
Join Capteur On Capteur.id_personnel = Personnel.id_personnel
Join Gaz On Gaz.id_gaz = Capteur.id_gaz
Where Gaz.gaz_type = "GESI" And Poste.post_nom = "Assistés d agents techniques";

7. Titres et dates des rapports concernant des concentrations de NH3, classés par ordre anti-chronologique

Select Distinct rap_titre, d_date
From Rapport
Join Rapporter On Rapporter.id_rapport = Rapport.id_rapport
Join Releve On Releve.id_releve = Rapporter.id_releve
Join Gaz On Gaz.id_gaz = Releve.id_gaz
Join Dates On Dates.id_date = Rapport.id_date
Where Gaz.gaz_sigle = 'NH3'
Order By Dates.d_date Desc;

8. Afficher le mois où la concentration de PFC a été la moins importante pour chaque région

Select 
    ag_region As region,
    d_date As mois
From Releve
Join Capteur On Capteur.id_capteur = Releve.id_capteur
Join Gaz On Gaz.id_gaz = Capteur.id_gaz
Join Agence On Agence.id_agence = Capteur.id_agence
Join Dates On Dates.id_date = Releve.id_date
Where gaz_sigle = 'PFC'
And (Agence.ag_region, rel_donnee) In (
    Select 
        ag_region,
        MIN(rel_donnee)
    From Releve
    Join Capteur On Capteur.id_capteur = Releve.id_capteur
    Join Gaz On Gaz.id_gaz = Capteur.id_gaz
    Join Agence On Agence.id_agence = Capteur.id_agence
    Where gaz_sigle = 'PFC'
    Group By Agence.ag_region
);

9. Moyenne des concentrations (en ppm) dans la région « Ile-de-France » en 2020, pour chaque gaz étudié

Select AVG(rel_donnee), Gaz.gaz_nom
From Releve
Join Agence On Agence.id_agence = Releve.id_agence
Join Dates On Dates.id_date = Releve.id_date
Join Gaz On Gaz.id_gaz = Releve.id_gaz
Where Agence.ag_region = "Île de France" And Dates.d_date Between '2020-01-01' And '2020-12-31'
Group By gaz_nom;

10. Taux de productivité des agents administratifs de l’agence de Toulouse (le taux est calculé en nombre de rapports écrits par mois en moyenne, sur la durée de leur contrat)

Select 
    pers_nom,
    pers_prenom,
    COUNT(Distinct id_rapport) / TIMESTAMPDIFF(MONTH, d_poste.d_date, CURDATE()) As productivite_mensuelle
From Personnel
Natural Join Adresse
Natural Join Ville
Natural Join Agence
Natural Join Poste
Join Dates As d_poste On Personnel.id_date_poste = d_poste.id_date
Left Join rediger USING(id_personnel)
Where ville_nom = "Toulouse"
  And post_nom Like "%administratif%"
Group By id_personnel;

11. Pour un gaz donné, liste des rapports contenant des données qui le concernent (on doit pouvoir donner le nom du gaz en paramètre)

Set @nomGaz = "CO2";

SELECT DISTINCT rap.id_rapport, rap.rap_titre
FROM Gaz
Join Releve On Releve.id_gaz = Gaz.id_gaz
JOIN Rapporter On Rapporter.id_releve = Releve.id_releve
JOIN Rapport AS rap On rap.id_rapport = Rapporter.id_rapport
WHERE gaz_sigle = @nomGaz;

12. Liste des régions dans lesquelles il y a plus de capteurs que de personnel d’agence

Select ag_region
From (
    Select ag_region, COUNT(id_capteur) As nb_capteurs
    From Capteur
    Natural Join Agence
    Group By ag_region
) As CapteursParRegion
Natural Join (
    Select ag_region, COUNT(id_personnel) As nb_personnel
    From Personnel
    Natural Join Agence
    Group By ag_region
) As PersonnelParRegion
Where nb_capteurs > nb_personnel;

3. Fonctionnalités

Comptes utilisateurs

Pour créer des comptes utilisateurs, nous devons donner un nom au compte, ainsi qu’un mot de passe, si nécessaire. Nous devons aussi sélectionner ses droits d’accès.

Alors, nous devions créer 2 comptes utilisateurs pour les accès et les droits sur la base de données. Un compte Admin et un compte User :

Le compte Admin a accès à touts les droits de la base de données. Tandis que le compte User ne peut qu’afficher et insérer des enregistrements, ainsi que visualiser la base de données.

Voici les privilèges du compte User :

Insertion d’un rapport

Les agents doivent être capable d’insérer des rapports dans la base de donnée.

Syntaxe générale de la requête pour y insérer des rapports :

Insert Into Rapport (rap_titre, rap_analyse, id_date, id_agence)
Values ('valeur1', 'valeur2', (Select id_date From Dates Where d_date = 'valeur3'), (Select id_agence From Agence Where ag_region = valeur4));

Avec :

Syntaxe générale de la requête pour mentionner le personnel et les relevés impliqués :

-- Mention des relevés
INSERT INTO rapporter (id_rapport, id_releve) VALUES (valeur5, 'valeur6');

-- Mention du personnel ayant rédigé le rapport
INSERT INTO rediger (id_rapport, id_personnel) VALUES (valeur5, valeur7);

Avec :

4. Conclusion

Nous avons pu concevoir une base de données qui répond à toutes les demandes. Cependant, quelques points d’amélioration peuvent encore être réalisé. En effet, dans le MCD, on pourrait rajouter une entitée “Type de gaz” pour éviter les redondances, dans l’entitée agence nous pourrions aussi rajouter un “nom d’agence”.
Ensuite, les requêtes auraient pu être plus optimisés, permettant une utilisation de la BDD ayant un impact réduit sur le climat, car des requêtes optimisées nécessitent moins de ressources de la part du server.
Puis lors du peuplement de la base de données, nous aurions pu insérer un plus grand nombre de données, pour tester plus en profondeur les nombreuses requêtes. Malgrés, ces points d’amélioration non réalisés, la base de données est bien fonctionnelle.